Efficiently loads large amounts of data into a DB2® table. The LOAD command loads data at the page level, bypasses trigger firing and logging, and delays constraint checking and index building until after the data is loaded into the DB2 table.
Data stored on the server can be in the form of a file, tape, or named pipe. Data can also be loaded from a cursor defined from a query running against the currently connected database, a different database, or by using a user-written script or application. If the COMPRESS attribute for the table is set to YES, the data loaded is subject to compression on every data and database partition for which a dictionary exists in the table, including data in the XML storage object of the table.
Quick link to File type modifiers for the load utility.
The load utility does not support loading data at the hierarchy level. The load utility is not compatible with range-clustered tables. The load utility does not support the NOT LOGGED INITIALLY parameter for the CREATE TABLE or ALTER TABLE statements.
This command can be issued against multiple database partitions in a single request.
When you load data into a table with protected rows, the target table has one column with a data type of DB2SECURITYLABEL. If the input row of data does not contain a value for that column, that row is rejected unless the usedefaults file type modifier is specified in the load command, in which case the security label you hold for write access from the security policy protecting the table is used. If you do not hold a security label for write access, the row is rejected and processing continues on to the next row
Since all load processes (and all DB2 server processes, in general) are owned by the instance owner, and all of these processes use the identification of the instance owner to access needed files, the instance owner must have read access to input data files. These input data files must be readable by the instance owner, regardless of who invokes the command.
Database.
Instance. An explicit attachment is not required. If a connection to the database has been established, an implicit attachment to the local instance is attempted.
>>-LOAD--FROM---------------------------------------------------> .-,---------------------------------------------------. V | >----+-filename----------------------------------------+-+------> +-pipename----------------------------------------+ +-device------------------------------------------+ +-(--query-statement--)---------------------------+ '-(--DATABASE--database-alias--query-statement--)-' >--OF--filetype--+-------------------------+--------------------> | .-,--------. | | V | | '-LOBS FROM----lob-path-+-' >--+------------------------+-----------------------------------> | .-,--------. | | V | | '-XML FROM----xml-path-+-' >--+--------------------------------+---------------------------> | .---------------. | | V | | '-MODIFIED BY----file-type-mod-+-' >--+-------------------------------------------------------------------------------------------------+--> | .-,------------------. | | V | | '-METHOD--+-L--(----col-start--col-end-+--)--+------------------------------------------------+-+-' | | .-,-------------------. | | | | V | | | | '-NULL INDICATORS--(----null-indicator-list-+--)-' | | .-,--------. | | V | | +-N--(----col-name-+--)---------------------------------------------------------------+ | .-,------------. | | V | | '-P--(----col-position-+--)-----------------------------------------------------------' >--+------------------------------------+-----------------------> '-XMLPARSE--+-STRIP----+--WHITESPACE-' '-PRESERVE-' >--+--------------------------------------------------------------------------------------+--> '-XMLVALIDATE USING--+-XDS--+-----------------------+--| Ignore and Map parameters |-+-' | '-DEFAULT--schema-sqlid-' | +-SCHEMA--schema-sqlid------------------------------------------+ '-SCHEMALOCATION HINTS------------------------------------------' >--+--------------+--+-------------+--+-----------------+-------> '-SAVECOUNT--n-' '-ROWCOUNT--n-' '-WARNINGCOUNT--n-' >--+--------------------+--+-------------------------------+----> '-MESSAGES ON SERVER-' '-TEMPFILES PATH--temp-pathname-' >--+-INSERT---------------------------+-------------------------> | .-KEEPDICTIONARY------. | +-REPLACE--+---------------------+-+ | +-RESETDICTIONARY-----+ | | '-RESETDICTIONARYONLY-' | +-RESTART--------------------------+ '-TERMINATE------------------------' >--INTO--table-name--+-------------------------+----------------> | .-,-------------. | | V | | '-(----insert-column-+--)-' >--+----------------------------------------------------------+--> | .-,-------------------------. | | V (1) (2) | | '-FOR EXCEPTION--table-name--------------+-------------+-+-' +-NORANGEEXC--+ '-NOUNIQUEEXC-' (3) .-STATISTICS NO----------. >--+------------------------+-----------------------------------> '-STATISTICS USE PROFILE-' >--+-----------------------------------------------------------------+--> | .-NO----------------------------------------------------. | +-COPY--+-YES--+-USE TSM--+--------------------------+--------+-+-+ | | '-OPEN--num-sess--SESSIONS-' | | | | .-,----------------. | | | | V | | | | +-TO----device/directory-+---------------------+ | | '-LOAD--lib-name--+--------------------------+-' | | '-OPEN--num-sess--SESSIONS-' | '-NONRECOVERABLE--------------------------------------------------' .-WITHOUT PROMPTING-. >--+-------------------+--+--------------------------+----------> '-DATA BUFFER--buffer-size-' >--+--------------------------+--+--------------------+---------> '-SORT BUFFER--buffer-size-' '-CPU_PARALLELISM--n-' >--+---------------------+--------------------------------------> '-DISK_PARALLELISM--n-' >--+--------------------------------+---------------------------> '-INDEXING MODE--+-AUTOSELECT--+-' +-REBUILD-----+ +-INCREMENTAL-+ '-DEFERRED----' .-ALLOW NO ACCESS-----------------------------. >--+---------------------------------------------+--------------> '-ALLOW READ ACCESS--+----------------------+-' '-USE--tablespace-name-' >--+----------------------------+-------------------------------> | .-YES-. | '-FETCH_PARALLELISM--+-NO--+-' >--+----------------------------------------------+-------------> '-SET INTEGRITY PENDING CASCADE--+-IMMEDIATE-+-' '-DEFERRED--' >--+-----------------+------------------------------------------> '-LOCK WITH FORCE-' >--+-----------------------------------------------------------------------------------+--> '-SOURCEUSEREXIT--executable--| Redirect Input/Output parameters |--+-------------+-' '-PARALLELIZE-' >--+------------------------------------------------------+---->< | .-----------------------. | | .-PARTITIONED DB CONFIG-. V | | '-+-----------------------+----partitioned-db-option-+-' Ignore and Map parameters |--+--------------------------------+---------------------------> | .-,------------. | | V | | '-IGNORE--(----schema-sqlid-+--)-' >--+----------------------------------------------------+-------| | .-,-----------------------------------. | | V | | '-MAP--(----(--schema-sqlid--,--schema-sqlid--)-+--)-' Redirect Input/Output parameters |--+-------------------------------------------------------------------------------------+--| '-REDIRECT--+-INPUT FROM--+-BUFFER--input-buffer-+--+-----------------------------+-+-' | '-FILE--input-file-----' '-OUTPUT TO FILE--output-file-' | '-OUTPUT TO FILE--output-file-------------------------------------------'
LOAD FROM (SELECT * FROM T1) OF CURSOR INSERT INTO T2
When the DATABASE database-alias clause is included before the query statement in the parentheses, the LOAD command will attempt to load the data using the query-statement from the given database as indicated by the database-alias name. Note that the LOAD will be executed using the user ID and password explicitly provided for the currently connected database (an implicit connection will cause the LOAD to fail).
If the input source is a file, pipe, or device, it must be accessible from the coordinator partition on the server.
If several names are specified, they will be processed in sequence. If the last item specified is a tape device and the user is prompted for a tape, the LOAD will fail and the ADMIN_CMD procedure will return an error.
This option is ignored when specified in conjunction with the CURSOR file type.
A value of Y in the NULL indicator column specifies that the column data is NULL. Any character other than Y in the NULL indicator column specifies that the column data is not NULL, and that column data specified by the METHOD L option will be loaded.
The NULL indicator character can be changed using the MODIFIED BY option.
db2 import from datafile1.del of del method P(1, 3, 4)
replace into table1 (c1, c3, c4)
The DEFAULT, IGNORE, and MAP clauses can be used to modify the schema determination behavior. These three optional clauses apply directly to the specifications of the XDS, and not to each other. For example, if a schema is selected because it is specified by the DEFAULT clause, it will not be ignored if also specified by the IGNORE clause. Similarly, if a schema is selected because it is specified as the first part of a pair in the MAP clause, it will not be re-mapped if also specified in the second part of another MAP clause pair.
If a schema is specified in the IGNORE clause, it cannot also be present in the left side of a schema pair in the MAP clause.
The IGNORE clause applies only to the XDS. A schema that is mapped by the MAP clause will not be subsequently ignored if specified by the IGNORE clause.
The DEFAULT clause takes precedence over the IGNORE and MAP clauses. If an XDS satisfies the DEFAULT clause, the IGNORE and MAP specifications will be ignored.
If a schema is present in the left side of a schema pair in the MAP clause, it cannot also be specified in the IGNORE clause.
Once a schema pair mapping is applied, the result is final. The mapping operation is non-transitive, and therefore the schema chosen will not be subsequently applied to another schema pair mapping.
A schema cannot be mapped more than once, meaning that it cannot appear on the left side of more than one pair.
The default value is zero, meaning that no consistency points are established, unless necessary.
This option is not allowed when specified in conjunction with the CURSOR file type or when loading a table containing an XML column.
The SAVECOUNT parameter is not supported for column-organized tables.
If this clause is not specified, the message file will be deleted when the ADMIN_CMD procedure returns to the caller. The MSG_RETRIEVAL and MSG_REMOVAL column in the result set will contain null values.
Note that with or without the clause, the fenced user ID or the instance owner's primary group must have the authority to create files under the directory indicated by the DB2_UTIL_MSGPATH registry variable.
A LOAD INSERT operation into a column-organized table maintains table statistics by default if the table was empty at the start of the load operation.
You cannot use this parameter to load data into system-period temporal tables.
A LOAD REPLACE operation into a column-organized table maintains table statistics by default.
This option is the default for row-organized tables.
CALL SYSPROC.ADMIN_CMD('load from staff.del of del replace
keepdictionary into SAMPLE.STAFF statistics use profile
data buffer 8')
Compress | Table row data dictionary exists | XML storage object dictionary exists1 | Compression dictionary | Data compression |
---|---|---|---|---|
YES | YES | YES | Preserve table row data and XML dictionaries. | Data to be loaded is subject to compression. |
YES | YES | NO | Preserve table row data dictionary and build a new XML dictionary. | Table row data to be loaded is subject to compression. After XML dictionary is built, remaining XML data to be loaded is subject to compression. |
YES | NO | YES | Build table row data dictionary and preserve XML dictionary. | After table row data dictionary is built, remaining table row data to be loaded is subject to compression. XML data to be loaded is subject to compression. |
YES | NO | NO | Build new table row data and XML dictionaries. | After dictionaries are built, remaining data to be loaded is subject to compression. |
NO | YES | YES | Preserve table row data and XML dictionaries. | Data to be loaded is not compressed. |
NO | YES | NO | Preserve table row data dictionary. | Data to be loaded is not compressed. |
NO | NO | YES | No effect on table row dictionary. Preserve XML dictionary. | Data to be loaded is not compressed. |
NO | NO | NO | No effect. | Data to be loaded is not compressed. |
This option is the default for column-organized tables.
CALL SYSPROC.ADMIN_CMD('load from staff.del of del replace
resetdictionary into SAMPLE.STAFF statistics use profile
data buffer 8')
Compress | Table row data dictionary exists | XML storage object dictionary exists1 | Compression dictionary | Data compression |
---|---|---|---|---|
YES | YES | YES | Build new dictionaries2. If the DATA CAPTURE CHANGES option is enabled on the CREATE TABLE or ALTER TABLE statements, the current table row data dictionary is kept (and referred to as the historical compression dictionary). | After dictionaries are built, remaining data to be loaded is subject to compression. |
YES | YES | NO | Build new dictionaries2. If the DATA CAPTURE CHANGES option is enabled on the CREATE TABLE or ALTER TABLE statements, the current table row data dictionary is kept (and referred to as the historical compression dictionary). | After dictionaries are built, remaining data to be loaded is subject to compression. |
YES | NO | YES | Build new dictionaries. | After dictionaries are built, remaining data to be loaded is subject to compression. |
YES | NO | NO | Build new dictionaries. | After dictionaries are built, remaining data to be loaded is subject to compression. |
NO | YES | YES | Remove dictionaries. | Data to be loaded is not compressed. |
NO | YES | NO | Remove table row data dictionary. | Data to be loaded is not compressed. |
NO | NO | YES | Remove XML storage object dictionary. | Data to be loaded is not compressed. |
NO | NO | NO | No effect. | All table data is not compressed. |
The LOAD TERMINATE option will not remove a backup pending state from table spaces.
The RESTART parameter is not supported for column-organized tables. To recover a column-organized table after a failed load operation, use the TERMINATE or REPLACE parameter.
db2 load from delfile1 of del
insert into table1 (c1, c2, c3,...)
db2 load from delfile1 of del modified by implicitlyhiddeninclude
insert into table1
db2set DB2_DMU_DEFAULT=IMPLICITLYHIDDENINCLUDE
db2 load from delfile1 of del insert into table1
CALL SYSPROC.ADMIN_CMD('load from delfile1 of del noheader
method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
insert into table1 (BLOB1, S2, I3, Int 4, I5, I6, DT7, I8, TM9)')
CALL SYSPROC.ADMIN_CMD('load from delfile1 of del noheader
method P (1, 2, 3, 4, 5, 6, 7, 8, 9)
insert into table1 (BLOB1, S2, I3, "Int 4", I5, I6, DT7, I8, TM9)')
Information that is written to the exception table is not written to the dump file. In a partitioned database environment, an exception table must be defined for those database partitions on which the loading table is defined. The dump file, otherwise, contains rows that cannot be loaded because they are invalid or have syntax errors.
During load, distribution statistics are not collected for columns of type XML.
LOAD with COPY NO on a recoverable database leaves the table spaces in a backup pending state. For example, performing a LOAD with COPY NO and INDEXING MODE DEFERRED will leave indexes needing a refresh. Certain queries on the table might require an index scan and will not succeed until the indexes are refreshed. The index cannot be refreshed if it resides in a table space which is in the backup pending state. In that case, access to the table will not be allowed until a backup is taken. Index refresh is done automatically by the database when the index is accessed by a query. If one of COPY NO, COPY YES, or NONRECOVERABLE is not specified, and the database is recoverable (logarchmeth1 or logarchmeth2 is set to value other than OFF), then COPY NO is the default.
With this option, table spaces are not put in backup pending state following the load operation, and a copy of the loaded data does not have to be made during the load operation. If one of COPY NO, COPY YES, or NONRECOVERABLE is not specified, and the database is not recoverable (logarchmeth1 and logarchmeth2 are both set to OFF), then NONRECOVERABLE is the default.
This is the default. Any actions which normally require user intervention will return an error message.
This memory is allocated directly from the utility heap, whose size can be modified through the util_heap_sz database configuration parameter. Beginning in version 9.5, the value of the DATA BUFFER option of the LOAD command can temporarily exceed util_heap_sz if more memory is available in the system. In this situation, the utility heap is dynamically increased as needed until the database_memory limit is reached. This memory will be released once the load operation completes.
If a value is not specified, an intelligent default is calculated by the utility at run time. The default is based on a percentage of the free space available in the utility heap at the instantiation time of the loader, as well as some characteristics of the table.
If the LogIndexBuild database configuration parameter is turned on, the transaction log contains the image of each index page after it is created. If the LogIndexBuild database configuration parameter is turned off, only the allocation and initialization of each page is logged by the Index Manager (about 250 bytes per page approximately as opposed to the non-empty portion of each page).
If the LogIndexBuild database configuration parameter is turned on, the DB2 software generates the log records for the insertion of every key into the index as well as any page splits performed. If this parameter is turned off (which is common when not using HADR), the amount of index logging performed by the Index Manager depends on whether or not the ALLOW READ ACCESS option was specified. If the ALLOW READ ACCESS option is specified, the log record is generated including logs for page splits. If the ALLOW READ ACCESS option is not specified, no log record from the Index Manager is generated.
Deferred indexing is only supported for tables with non-unique indexes, so that duplicate keys inserted during the load phase are not persistent after the load operation.
When there are constraints on the table, the table state will be set to Set Integrity Pending as well as Load In Progress. The SET INTEGRITY statement must be used to take the table out of Set Integrity Pending state.
The ALLOW READ ACCESS parameter is not supported for column-organized tables.
LOAD TERMINATE or LOAD RESTART of an ALLOW READ ACCESS load can use this parameter; LOAD TERMINATE or LOAD RESTART of an ALLOW NO ACCESS load cannot use this parameter. Furthermore, this option is not valid if the indexes on the target table are marked as requiring a rebuild.
When there are constraints on the table, the table state will be set to Set Integrity Pending as well as Load In Progress, and Read Access. At the end of the load, the table state Load In Progress will be removed but the table states Set Integrity Pending and Read Access will remain. The SET INTEGRITY statement must be used to take the table out of Set Integrity Pending. While the table is in Set Integrity Pending and Read Access states, the non-delta portion of the data is still accessible to readers, the new (delta) portion of the data will remain inaccessible until the SET INTEGRITY statement has completed. A user can perform multiple loads on the same table without issuing a SET INTEGRITY statement. Only the original (checked) data will remain visible, however, until the SET INTEGRITY statement is issued.
Without this option the shadow index is built in the same table space as the original. Since both the original index and shadow index by default reside in the same table space simultaneously, there might be insufficient space to hold both indexes within one table space. Using this option ensures that you retain enough table space for the indexes.
This option is ignored if the user does not specify INDEXING MODE REBUILD or INDEXING MODE AUTOSELECT. This option will also be ignored if INDEXING MODE AUTOSELECT is chosen and load chooses to incrementally update the index.
When the loaded table is later checked for constraint violations (using the IMMEDIATE CHECKED option of the SET INTEGRITY statement), descendent foreign key tables that were placed in Set Integrity Pending Read Access state will be put into Set Integrity Pending No Access state.
Descendent foreign key tables might later be implicitly placed in Set Integrity Pending state when their parent tables are checked for constraint violations (using the IMMEDIATE CHECKED option of the SET INTEGRITY statement). Descendent immediate materialized query tables and descendent immediate staging tables will be implicitly placed in Set Integrity Pending state when one of its underlying tables is checked for integrity violations. A query of a table that is in the Set Integrity Pending state might succeed if an eligible materialized query table that is not in the Set Integrity Pending state is accessed by the query instead of the specified table. A warning (SQLSTATE 01586) will be issued to indicate that descendent tables have been placed in Set Integrity Pending state. See the Notes section of the SET INTEGRITY statement in the SQL Reference for when these descendent tables will be put into Set Integrity Pending state.
If the SET INTEGRITY PENDING CASCADE option is not specified:
If LOAD does not put the target table into Set Integrity Pending state, the SET INTEGRITY PENDING CASCADE option is ignored.
ALLOW NO ACCESS loads might force applications holding conflicting locks at the start of the load operation. At the start of the load the utility can force applications that are attempting to either query or modify the table.
ALLOW READ ACCESS loads can force applications holding conflicting locks at the start or end of the load operation. At the start of the load the load utility can force applications that are attempting to modify the table. At the end of the load operation, the load utility can force applications that are attempting to either query or modify the table.
The SOURCEUSEREXIT parameter is not supported for column-organized tables.
PART_FILE_LOCATION x
OUTPUT_DBPARTNUMS x
PARTITIONING_DBPARTNUMS x
MODE x
MAX_NUM_PART_AGENTS x
ISOLATE_PART_ERRS x
STATUS_INTERVAL x
PORT_RANGE x
CHECK_TRUNCATION
MAP_FILE_INPUT x
MAP_FILE_OUTPUT x
TRACE x
NEWLINE
DISTFILE x
OMIT_HEADER
RUN_STAT_DBPARTNUM x
Detailed descriptions
of these options are provided in "Load configuration options for
partitioned database environments". CALL SYSPROC.ADMIN_CMD('LOAD FROM /home/theresax/tmp/emp_exp.dat
OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)
MESSAGES /home/theresax/tmp/emp_load.msg
REPLACE INTO THERESAX.EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME,
WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY,
BONUS, COMM) COPY NO INDEXING MODE AUTOSELECT ISOLATE_PART_ERRS
LOAD_ERRS_ONLY MODE PARTITION_AND_LOAD' )
Result set 1
--------------
ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED ...
---------...- ------------...- -----------...- -------------...- ...
32 0 32 0 ...
1 record(s) selected.
Return Status = 0
... ROWS_DELETED ROWS_COMMITTED MSG_RETRIEVAL
... ------------...- --------------...- ------------------------------...-
... 0 32 SELECT SQLCODE, MSG_TEXT FROM
... TABLE(SYSPROC.ADMIN_GET_MSGS(
... '2203498_thx')) AS MSG
... MSG_REMOVAL
... --------------------------------------------...-
... CALL SYSPROC.ADMIN_REMOVE_MSGS('2203498_thx')
...
Result set 1
--------------
ROWS_READ ROWS_REJECTED ROWS_PARTITIONED NUM_AGENTINFO_ENTRIES ...
---------...- -------------...- ----------------...- --------------------- ...
32 0 32 5 ...
...
...
1 record(s) selected.
... MSG_RETRIEVAL MSG_REMOVAL
... ----------------------------------...- -----------------------------...-
... SELECT DBPARTITIONNUM, AGENT_TYPE, CALL SYSPROC.ADMIN_REMOVE_MSGS
... SQLCODE, MSG_TEXT FROM TABLE ('2203498_thx')
... (SYSPROC.ADMIN_GET_MSGS
... ('2203498_thx')) AS MSG
Result set 2
--------------
DBPARTITIONNUM SQLCODE TABSTATE AGENTTYPE
--------------...- -------...- --------...- ------------...-
10 0 NORMAL LOAD
20 0 NORMAL LOAD
30 0 NORMAL LOAD
20 0 NORMAL PARTITION
10 0 NORMAL PRE_PARTITION
1 record(s) selected.
Return Status = 0
The user has constructed a data file with XDS fields to describe the documents that are to be inserted into the table. It might appear like this :
1, "<XDS FIL=""file1.xml"" />"
2, "<XDS FIL='file2.xml' OFF='23' LEN='45' />"
For the first row, the XML document is identified by the file named file1.xml. Note that since the character delimiter is the double quote character, and double quotation marks exist inside the XDS, the double quotation marks contained within the XDS are doubled. For the second row, the XML document is identified by the file named file2.xml, and starts at byte offset 23, and is 45 bytes in length.
LOAD
FROM data.del of DEL INSERT INTO mytable
Loading data from cursor is the same as with a regular relational column type. The user has two tables, T1 and T2, each of which consist of a single XML column named C1. To LOAD from T1 into T2, the user will first declare a cursor:
DECLARE
X1 CURSOR FOR SELECT C1 FROM T1;
Next, the user may issue a LOAD using the cursor type:
LOAD FROM X1 of
CURSOR INSERT INTO T2
Applying the XML specific LOAD options to the cursor type is the same as loading from a file.
The following chart summarizes the compression dictionary management behavior for LOAD processing under the TERMINATE directive.
Table COMPRESS attribute | Does table row data dictionary existed before LOAD? | XML storage object dictionary exists before LOAD1 | TERMINATE: LOAD REPLACE KEEPDICTIONARY or LOAD INSERT | TERMINATE: LOAD REPLACE RESETDICTIONARY |
---|---|---|---|---|
YES | YES | YES | Keep existing dictionaries. | Neither dictionary is kept. 2 |
YES | YES | NO | Keep existing dictionary. | Nothing is kept. 2 |
YES | NO | YES | Keep existing dictionary. | Nothing is kept. |
YES | NO | NO | Nothing is kept. | Nothing is kept. |
NO | YES | YES | Keep existing dictionaries. | Nothing is kept. |
NO | YES | NO | Keep existing dictionary. | Nothing is kept. |
NO | NO | YES | Keep existing dictionary. | Nothing is kept. |
NO | NO | NO | Do nothing. | Do nothing. |
LOAD RESTART truncates a table up to the last consistency point reached. As part of LOAD RESTART processing, a compression dictionary will exist in the table if it was present in the table at the time the last LOAD consistency point was taken. In that case, LOAD RESTART will not create a new dictionary. For a summary of the possible conditions, see Table 4.
Table COMPRESS Attribute | Table row data dictionary exist before LOAD consistency point?1 | XML Storage object dictionary existed before last LOAD?2 | RESTART: LOAD REPLACE KEEPDICTIONARY or LOAD INSERT | RESTART: LOAD REPLACE RESETDICTIONARY |
---|---|---|---|---|
YES | YES | YES | Keep existing dictionaries. | Keep existing dictionaries. |
YES | YES | NO | Keep existing table row data dictionary and build XML dictionary subject to ADC. | Keep existing table row data dictionary and build XML dictionary. |
YES | NO | YES | Build table row data dictionary subject to ADC. Keep existing XML dictionary. | Build table row data dictionary. Keep existing XML dictionary. |
YES | NO | NO | Build table row data and XML dictionaries subject to ADC. | Build table row data and XML dictionaries. |
NO | YES | YES | Keep existing dictionaries. | Remove existing dictionaries. |
NO | YES | NO | Keep existing table row data dictionary. | Remove existing table row data dictionary. |
NO | NO | YES | Keep existing XML dictionary. | Remove existing XML dictionary. |
NO | NO | NO | Do nothing. | Do nothing. |
Column name | Data type | Description |
---|---|---|
ROWS_READ | BIGINT | Number of rows read during the load operation. |
ROWS_SKIPPED | BIGINT | Number of rows skipped before the load operation started. This information is returned for a single-partition database only. |
ROWS_LOADED | BIGINT | Number of rows loaded into the target table. This information is returned for a single-partition database only. |
ROWS_REJECTED | BIGINT | Number of rows that could not be loaded into the target table. |
ROWS_DELETED | BIGINT | Number of duplicate rows that were not loaded into the target table. This information is returned for a single-partition database only. |
ROWS_COMMITTED | BIGINT | Total number of rows processed: the number of rows successfully loaded into the target table, plus the number of skipped and rejected rows. This information is returned for a single-partition database only. |
ROWS_PARTITIONED | BIGINT | Number of rows distributed by all database distributing agents. This information is returned for a multi-partition database only. |
NUM_AGENTINFO_ENTRIES | BIGINT | Number of entries returned in the second result set for a multi-partition database. This is the number of agent information entries produced by the load operation. This information is returned for multi-partition database only. |
MSG_RETRIEVAL | VARCHAR(512) | SQL statement that is used to retrieve messages
created by this utility. For example,
This information is returned
only if the MESSAGES ON SERVER clause is specified. |
MSG_REMOVAL | VARCHAR(512) | SQL statement that is used to clean up messages
created by this utility. For example:
This information is returned only if
the MESSAGES ON SERVER clause is specified. |
Column name | Data type | Description |
---|---|---|
DBPARTITIONNUM | SMALLINT | The database partition number on which the agent executed the load operation. |
SQLCODE | INTEGER | Final SQLCODE resulting from the load processing. |
TABSTATE | VARCHAR(20) | Table state after load operation has completed.
It is one of:
Note: Not all possible table states are returned by this interface.
|
AGENTTYPE | VARCHAR(20) | Agent type and is one of:
|
Modifier | Description |
---|---|
anyorder | This modifier specifies that the preservation of the source data order is not required. Using this modifier yields significant additional performance benefits on SMP systems. Use this modifier with the cpu_parallelism parameter. If the value of the cpu_parallelism parameter is 1, this modifier is ignored. This modifier is not supported if the value of the SAVECOUNT parameter is greater the 0, because crash recovery after a consistency point requires that data be loaded in sequence. This modifier is implicitly turned on for all load operations for column-organized tables, multidimensional clustering (MDC) tables, and range-partitioned tables. |
generatedignore | This modifier informs the load utility that data for all generated columns is present in the data file but should be ignored. This results in all generated column values being generated by the utility. This modifier cannot be used with either the generatedmissing or the generatedoverride modifier. |
generatedmissing | If this modifier is specified, the utility assumes that the input data file contains no data for the generated column (not even NULLs). This results in all generated column values being generated by the utility. This modifier cannot be used with either the generatedignore or the generatedoverride modifier. |
generatedoverride | This modifier instructs the load utility to accept user-supplied
data for all generated columns in the table (contrary to the normal
rules for these types of columns). This is useful when migrating data
from another database system, or when loading a table from data that
was recovered using the RECOVER DROPPED TABLE option
on the ROLLFORWARD DATABASE command. When this
modifier is used, any rows with no data or NULL data for a non-nullable
generated column will be rejected (SQL3116W). When this modifier
is used, the table will be placed in Set Integrity Pending state.
To take the table out of Set Integrity Pending state without verifying
the user-supplied values, issue the following command after the load
operation:
To take the table out of Set Integrity
Pending state and force verification of the user-supplied values,
issue the following command after the load operation:
When this modifier is specified and there is a generated column in any of the partitioning keys, dimension keys or distribution keys, then the LOAD command will automatically convert the modifier to generatedignore and proceed with the load. This will have the effect of regenerating all of the generated column values. This modifier cannot be used with either the generatedmissing or the generatedignore modifier. |
identityignore | This modifier informs the load utility that data for the identity column is present in the data file but should be ignored. This results in all identity values being generated by the utility. The behavior will be the same for both GENERATED ALWAYS and GENERATED BY DEFAULT identity columns. This means that for GENERATED ALWAYS columns, no rows will be rejected. This modifier cannot be used with either the identitymissing or the identityoverride modifier. |
identitymissing | If this modifier is specified, the utility assumes that the input data file contains no data for the identity column (not even NULLs), and will therefore generate a value for each row. The behavior will be the same for both GENERATED ALWAYS and GENERATED BY DEFAULT identity columns. This modifier cannot be used with either the identityignore or the identityoverride modifier. |
identityoverride | This modifier should be used only when an identity column defined as GENERATED ALWAYS is present in the table to be loaded. It instructs the utility to accept explicit, non-NULL data for such a column (contrary to the normal rules for these types of identity columns). This is useful when migrating data from another database system when the table must be defined as GENERATED ALWAYS, or when loading a table from data that was recovered using the DROPPED TABLE RECOVERY option on the ROLLFORWARD DATABASE command. When this modifier is used, any rows with no data or NULL data for the identity column will be rejected (SQL3116W). This modifier cannot be used with either the identitymissing or the identityignore modifier. The load utility will not attempt to maintain or verify the uniqueness of values in the table's identity column when this option is used. |
implicitlyhiddeninclude | If this modifier is specified, the utility assumes that the input data file contains data for the implicitly hidden columns and this data will also be loaded. This modifier cannot be used with the implicitlyhiddenmissing modifier. See the Note: section for information about the precedence when multiple modifiers are specified. |
implicitlyhiddenmissing | If this modifier is specified, the utility assumes that the input data file does not contain data for the implicitly hidden columns and the utility will generate values for those hidden columns. This modifier cannot be used with the implicitlyhiddeninclude modifier. See the Note: section for information about the precedence when multiple modifiers are specified. |
indexfreespace=x | x is an integer between 0 and 99 inclusive.
The value is interpreted as the percentage of each index page that
is to be left as free space when load rebuilds the index. Load with INDEXING
MODE INCREMENTAL ignores this option. The first entry in
a page is added without restriction; subsequent entries are added
to maintain the percent free space threshold. The default value is
the one used at CREATE INDEX time. This value takes precedence over the PCTFREE value specified in the CREATE INDEX statement. The indexfreespace option affects index leaf pages only. |
lobsinfile | lob-path specifies the path to the files
containing LOB data. The ASC, DEL, or IXF load input files contain
the names of the files having LOB data in the LOB column. This option is not supported in conjunction with the CURSOR filetype. The LOBS FROM clause specifies where the LOB files are located when the lobsinfile modifier is used. The LOBS FROM clause will implicitly activate the lobsinfile behavior. The LOBS FROM clause conveys to the LOAD utility the list of paths to search for the LOB files while loading the data. Each path contains at least one file that contains at least one LOB pointed to by a Lob Location Specifier (LLS) in the data file. The LLS is a string representation of the location of a LOB in a file stored in the LOB file path. The format of an LLS is filename.ext.nnn.mmm/, where filename.ext is the name of the file that contains the LOB, nnn is the offset in bytes of the LOB within the file, and mmm is the length of the LOB in bytes. For example, if the string db2exp.001.123.456/ is stored in the data file, the LOB is located at offset 123 in the file db2exp.001, and is 456 bytes long. To indicate a null LOB , enter the size as -1. If the size is specified as 0, it is treated as a 0 length LOB. For null LOBS with length of -1, the offset and the file name are ignored. For example, the LLS of a null LOB might be db2exp.001.7.-1/. |
noheader | Skips the header verification code (applicable only to load
operations into tables that reside in a single-partition database
partition group). If the default MPP load (mode PARTITION_AND_LOAD) is used against a table residing in a single-partition database partition group, the file is not expected to have a header. Thus the noheader modifier is not needed. If the LOAD_ONLY mode is used, the file is expected to have a header. The only circumstance in which you should need to use the noheader modifier is if you wanted to perform LOAD_ONLY operation using a file that does not have a header. |
norowwarnings | Suppresses all warnings about rejected rows. |
pagefreespace=x | x is an integer between 0 and 100 inclusive. The value is interpreted as the percentage of each data page that is to be left as free space. If the specified value is invalid because of the minimum row size, (for example, a row that is at least 3 000 bytes long, and an x value of 50), the row will be placed on a new page. If a value of 100 is specified, each row will reside on a new page. The PCTFREE value of a table determines the amount of free space designated per page. If a pagefreespace value on the load operation or a PCTFREE value on a table have not been set, the utility will fill up as much space as possible on each page. The value set by pagefreespace overrides the PCTFREE value specified for the table. |
periodignore | This modifier informs the load utility that data for the period columns is present in the data file but should be ignored. When this modifier is specified, all period column values are generated by the utility. This modifier cannot be used with the periodmissing modifier or the periodoverride modifier. |
periodmissing | If this modifier is specified, the utility assumes that the input data file contains no data for the period columns. When this modifier is specified, all period column values are generated by the utility. This modifier cannot be used with the periodignore modifier or the periodoverride modifier. |
periodoverride | This modifier instructs the load utility to accept user-supplied data for GENERATED ALWAYS AS ROW BEGIN and GENERATED ALWAYS AS ROW END columns in a system-period temporal table. This behavior is contrary to the normal rules for these types of columns. The modifier can be useful when you want to maintain history data and load data that includes time stamps into a system-period temporal table. When this modifier is used, any rows with no data or NULL data in a ROW BEGIN or ROW END column are rejected. |
rowchangetimestampignore | This modifier informs the load utility that data for the row change timestamp column is present in the data file but should be ignored. This results in all ROW CHANGE TIMESTAMPs being generated by the utility. The behavior will be the same for both GENERATED ALWAYS and GENERATED BY DEFAULT columns. This means that for GENERATED ALWAYS columns, no rows will be rejected. This modifier cannot be used with either the rowchangetimestampmissing or the rowchangetimestampoverride modifier. |
rowchangetimestampmissing | If this modifier is specified, the utility assumes that the input data file contains no data for the row change timestamp column (not even NULLs), and will therefore generate a value for each row. The behavior will be the same for both GENERATED ALWAYS and GENERATED BY DEFAULT columns. This modifier cannot be used with either the rowchangetimestampignore or the rowchangetimestampoverride modifier. |
rowchangetimestampoverride | This modifier should be used only when a row change timestamp column defined as GENERATED ALWAYS is present in the table to be loaded. It instructs the utility to accept explicit, non-NULL data for such a column (contrary to the normal rules for these types of row change timestamp columns). This is useful when migrating data from another database system when the table must be defined as GENERATED ALWAYS, or when loading a table from data that was recovered using the DROPPED TABLE RECOVERY option on the ROLLFORWARD DATABASE command. When this modifier is used, any rows with no data or NULL data for the ROW CHANGE TIMESTAMP column will be rejected (SQL3116W). This modifier cannot be used with either the rowchangetimestampmissing or the rowchangetimestampignore modifier. The load utility will not attempt to maintain or verify the uniqueness of values in the table's row change timestamp column when this option is used. |
seclabelchar | Indicates that security labels in the input
source file are in the string format for security label values rather
than in the default encoded numeric format. LOAD converts
each security label into the internal format as it is loaded. If a
string is not in the proper format the row is not loaded and a warning
(SQLSTATE 01H53, SQLCODE SQL3242W) is returned. If the string does
not represent a valid security label that is part of the security
policy protecting the table then the row is not loaded and a warning
(SQLSTATE 01H53, SQLCODE SQL3243W) is returned. This modifier cannot be specified if the seclabelname modifier is specified, otherwise the load fails and an error (SQLCODE SQL3525N) is returned. If you have a table consisting of a single DB2SECURITYLABEL column,
the data file might look like this:
To load or import this data,
the seclabelchar file type modifier must be used:
|
seclabelname | Indicates that security labels in the input
source file are indicated by their name rather than the default encoded
numeric format. LOAD will convert the name to the
appropriate security label if it exists. If no security label exists
with the indicated name for the security policy protecting the table
the row is not loaded and a warning (SQLSTATE 01H53, SQLCODE SQL3244W)
is returned. This modifier cannot be specified if the seclabelchar modifier is specified, otherwise the load fails and an error (SQLCODE SQL3525N) is returned. If you have a table consisting of a single DB2SECURITYLABEL column,
the data file might consist of security label names similar to:
To load or import this data, the seclabelname file
type modifier must be used:
Note: If
the file type is ASC, any spaces following the name of the security
label will be interpreted as being part of the name. To avoid this
use the striptblanks file type modifier to make
sure the spaces are removed.
|
totalfreespace=x | x is an integer greater than or equal to 0. The value is interpreted as the percentage of the total pages in the table that is to be appended to the end of the table as free space. For example, if x is 20, and the table has 100 data pages after the data has been loaded, 20 additional empty pages will be appended. The total number of data pages for the table will be 120. The data pages total does not factor in the number of index pages in the table. This option does not affect the index object. If two loads are done with this option specified, the second load will not reuse the extra space appended to the end by the first load. |
transactionidignore | This modifier informs the load utility that data for the TRANSACTION START ID column is present in the data file but should be ignored. When this modifier is specified, the value for the TRANSACTION START ID column is generated by the utility. This modifier cannot be used with the transactionidmissing modifier or the transactionidoverride modifier. |
transactionidmissing | If this modifier is specified, the utility assumes that the input data file contains no data for the TRANSACTION START ID columns. When this modifier is specified, the value for the TRANSACTION START ID column is generated by the utility. This modifier cannot be used with the transactionidignore modifier or the transactionidoverride modifier. |
transactionidoverride | This modifier instructs the load utility to accept user-supplied data for the GENERATED ALWAYS AS TRANSACTION START ID column in a system-period temporal table. This behavior is contrary to the normal rules for this type of column. When this modifier is used, any rows with no data or NULL data in a TRANSACTION START ID column are rejected. |
usedefaults | If a source column for a target table column has been specified,
but it contains no data for one or more row instances, default values
are loaded. Examples of missing data are:
|
Modifier | Description |
---|---|
codepage=x | x is an ASCII character string. The value
is interpreted as the code page of the data in the input data set.
Converts character data (and numeric data specified in characters)
from this code page to the database code page during the load operation.
The following rules apply:
This option is not supported in conjunction with the CURSOR filetype. |
dateformat="x" | x is the format of the date in the source
file.1 Valid date elements are:
A default
value of 1 is assigned for each element that is not specified. Some
examples of date formats are:
|
dumpfile = x | x is the fully qualified (according to the
server database partition) name of an exception file to which rejected
rows are written. A maximum of 32 KB of data is written per record.
The following section is an example that shows how to specify a dump
file:
The file will be created and owned by the instance owner. To override the default file permissions, use the dumpfileaccessall file type modifier. Note:
|
dumpfileaccessall | Grants read access to 'OTHERS' when a dump file is created.
This file type modifier is only valid when:
If the specified file already exists, its permissions will not be changed. |
fastparse | Use with caution. Reduces syntax checking on user-supplied column values, and enhances performance. Tables are guaranteed to be architecturally correct (the utility performs sufficient data checking to prevent a segmentation violation or trap), however, the coherence of the data is not validated. Only use this option if you are certain that your data is coherent and correct. For example, if the user-supplied data contains an invalid timestamp column value of :1>0-00-20-07.11.12.000000, this value is inserted into the table if fastparse is specified, and rejected if fastparse is not specified. |
implieddecimal | The location of an implied decimal point is determined by the
column definition; it is no longer assumed to be at the end of the
value. For example, the value 12345 is loaded into
a DECIMAL(8,2) column as 123.45, not 12345.00.
This modifier cannot be used with the packeddecimal modifier. |
timeformat="x" | x is the format of the time in the source
file.1 Valid time elements are:
A default value
of 0 is assigned for each element that is not specified. Some examples
of time formats are:
|
timestampformat="x" | x is the format of the time stamp in the
source file.1 Valid time stamp elements are:
|
timestampformat="x" (Continued) | A default value of 1 is assigned for unspecified
YYYY, M, MM, D, DD, or DDD elements. A default value of 'Jan' is
assigned to an unspecified MMM element. A default value of 0 is assigned
for all other unspecified elements. The following section is an example
of a time stamp format:
The valid values for the MMM element include: 'jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov' and 'dec'. These values are case insensitive. If the timestampformat modifier
is not specified, the load utility formats the timestamp field using
one of two possible formats:
The load utility chooses the format by looking at the separator between the DD and HH. If it is a dash '-', the load utility uses the regular dashes and dots format (YYYY-MM-DD-HH.MM.SS). If it is a blank space, then the load utility expects a colon ':' to separate the HH, MM and SS. In either format, if you include the microseconds field (UUUUUU), the load utility expects the dot '.' as the separator. Either YYYY-MM-DD-HH.MM.SS.UUUUUU or YYYY-MM-DD HH:MM:SS.UUUUUU are acceptable. The following example
illustrates how to load data containing user defined date and time
formats into a table called schedule:
|
usegraphiccodepage | If usegraphiccodepage is given, the assumption
is made that data being loaded into graphic or double-byte character
large object (DBCLOB) data field(s) is in the graphic code page.
The rest of the data is assumed to be in the character code page.
The graphic codepage is associated with the character code page.
LOAD determines the character code page through
either the codepage modifier, if it is specified,
or through the code page of the database if the codepage modifier
is not specified. This modifier should be used in conjunction with the delimited data file generated by drop table recovery only if the table being recovered has graphic data. Restrictions The usegraphiccodepage modifier MUST NOT be specified with DEL files created by the EXPORT utility, as these files contain data encoded in only one code page. The usegraphiccodepage modifier is also ignored by the double-byte character large objects (DBCLOBs) in files. |
xmlchar | Specifies that XML documents are encoded in
the character code page. This option is useful for processing XML documents that are encoded in the specified character code page but do not contain an encoding declaration. For each document, if a declaration tag exists and contains an encoding attribute, the encoding must match the character code page, otherwise the row containing the document will be rejected. Note that the character codepage is the value specified by the codepage file type modifier, or the application codepage if it is not specified. By default, either the documents are encoded in Unicode, or they contain a declaration tag with an encoding attribute. |
xmlgraphic | Specifies that XML documents are encoded in
the specified graphic code page. This option is useful for processing XML documents that are encoded in a specific graphic code page but do not contain an encoding declaration. For each document, if a declaration tag exists and contains an encoding attribute, the encoding must match the graphic code page, otherwise the row containing the document will be rejected. Note that the graphic code page is the graphic component of the value specified by the codepage file type modifier, or the graphic component of the application code page if it is not specified. By default, documents are either encoded in Unicode, or they contain a declaration tag with an encoding attribute. |
Modifier | Description |
---|---|
binarynumerics | Numeric (but not DECIMAL) data must be in binary form, not
the character representation. This avoids costly conversions. This option is supported only with positional ASC, using fixed length records specified by the reclen option. The following
rules apply:
NULLs cannot be present in the data for columns affected by this modifier. Blanks (normally interpreted as NULL) are interpreted as a binary value when this modifier is used. |
nochecklengths | If nochecklengths is specified, an attempt is made to load each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows can be successfully loaded if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions. |
nullindchar=x | x is a single character. Changes the character
denoting a NULL value to x. The default value of x is Y.2 This modifier is case sensitive for EBCDIC data files, except when the character is an English letter. For example, if the NULL indicator character is specified to be the letter N, then n is also recognized as a NULL indicator. |
packeddecimal | Loads packed-decimal data directly, since the binarynumerics modifier
does not include the DECIMAL field type. This option is supported only with positional ASC, using fixed length records specified by the reclen option. Supported values for
the sign nibble are:
NULLs cannot be present in the data for columns affected by this modifier. Blanks (normally interpreted as NULL) are interpreted as a binary value when this modifier is used. Regardless of the server platform, the byte order of binary data in the load source file is assumed to be big-endian; that is, when using this modifier on Windows operating systems, the byte order must not be reversed. This modifier cannot be used with the implieddecimal modifier. |
reclen=x | x is an integer with a maximum value of 32 767. x characters are read for each row, and a newline character is not used to indicate the end of the row. |
striptblanks | Truncates any trailing blank spaces when loading data into
a variable-length field. If this option is not specified, blank spaces
are kept. This option cannot be specified together with striptnulls. These are mutually exclusive options. This option replaces the obsolete t option, which is supported for earlier compatibility only. |
striptnulls | Truncates any trailing NULLs (0x00 characters) when loading
data into a variable-length field. If this option is not specified,
NULLs are kept. This option cannot be specified together with striptblanks. These are mutually exclusive options. This option replaces the obsolete padwithzero option, which is supported for earlier compatibility only. |
zoneddecimal | Loads zoned decimal data, since the binarynumerics modifier
does not include the DECIMAL field type. This option is supported
only with positional ASC, using fixed length records specified by
the reclen option. Half-byte sign values
can be one of the following value:
Supported values for digits are 0x0 to 0x9. Supported values for zones are 0x3 and 0xF. |
Modifier | Description |
---|---|
chardelx | x is a single character string delimiter.
The default value is a double quotation mark ("). The specified character
is used in place of double quotation marks to enclose a character
string.23 If you want to explicitly specify
the double quotation mark (") as the character string delimiter, you
should specify it as follows:
The
single quotation mark (') can also be specified as a character string
delimiter as follows:
|
coldelx | x is a single character column delimiter. The default value is a comma (,). The specified character is used in place of a comma to signal the end of a column.23 |
decplusblank | Plus sign character. Causes positive decimal values to be prefixed with a blank space instead of a plus sign (+). The default action is to prefix positive decimal values with a plus sign. |
decptx | x is a single character substitute for the period as a decimal point character. The default value is a period (.). The specified character is used in place of a period as a decimal point character.23 |
delprioritychar | The current default priority for delimiters is: record delimiter,
character delimiter, column delimiter. This modifier protects existing
applications that depend on the older priority by reverting the delimiter
priorities to: character delimiter, record delimiter, column delimiter.
Syntax:
For
example, given the following DEL data file:
With the delprioritychar modifier specified, there will be only two rows in this data file. The second <row delimiter> will be interpreted as part of the first data column of the second row, while the first and the third <row delimiter> are interpreted as actual record delimiters. If this modifier is not specified, there will be three rows in this data file, each delimited by a <row delimiter>. |
keepblanks | Preserves the leading and trailing blanks in each field of
type CHAR, VARCHAR, LONG VARCHAR, or CLOB. Without this option, all
leading and trailing blanks that are not inside character delimiters
are removed, and a NULL is inserted into the table for all blank
fields. The following example illustrates how to load data into
a table called TABLE1, while preserving all leading and trailing spaces
in the data file:
|
nochardel | The load utility will assume all bytes found between the column
delimiters to be part of the column's data. Character delimiters
will be parsed as part of column data. This option should not be
specified if the data was exported using a DB2 database system (unless nochardel was
specified at export time). It is provided to support vendor data
files that do not have character delimiters. Improper usage might
result in data loss or corruption. This option cannot be specified with chardelx, delprioritychar or nodoubledel. These are mutually exclusive options. |
nodoubledel | Suppresses recognition of double character delimiters. |
Modifier | Description |
---|---|
forcein | Directs the utility to accept data despite code page mismatches,
and to suppress translation between code pages. Fixed length target fields are checked to verify that they are large enough for the data. If nochecklengths is specified, no checking is done, and an attempt is made to load each row. |
nochecklengths | If nochecklengths is specified, an attempt is made to load each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows can be successfully loaded if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions. |
"M" (could be a month, or a minute)
"M:M" (Which is which?)
"M:YYYY:M" (Both are interpreted as month.)
"S:M:YYYY" (adjacent to both a time value and a date value)
In ambiguous cases, the utility will report an error message, and
the operation will fail."M:YYYY" (Month)
"S:M" (Minute)
"M:YYYY:S:M" (Month....Minute)
"M:H:YYYY:M:D" (Minute....Month)
Some characters, such as double quotation marks and back slashes, must be preceded by an escape character (for example, \).
... modified by coldel# ...
... modified by coldel0x23 ...
... modified by coldelX23 ...
db2 load from delfile1 of del modified by
implicitlyhiddeninclude identitymissing insert into table1
However,
changing the order of the file type modifiers in the following statement
means that data for all implicitly hidden columns (including hidden
identity columns) is included in the input data. While data for identity
columns that are not implicitly hidden is not.db2 load from delfile1 of del modified by
identitymissing implicitlyhiddeninclude insert into table1
codepage=N | usegraphiccodepage | LOAD behavior |
---|---|---|
Absent | Absent | All data in the file is assumed to be in the database code page, not the application code page, even if the CLIENT option is specified. |
Present | Absent | All data in the file is assumed to be in code page N.
Warning: Graphic data will be corrupted when loaded into the database if N is a single-byte code page. |
Absent | Present | Character data in the file is assumed to be in the database
code page, even if the CLIENT option is specified.
Graphic data is assumed to be in the code page of the database graphic
data, even if the CLIENT option is specified. If the database code page is single-byte, then all data is assumed to be in the database code page. Warning: Graphic data will be corrupted when loaded into a single-byte database. |
Present | Present | Character data is assumed to be in code page N.
Graphic data is assumed to be in the graphic code page of N. If N is a single-byte or double-byte code page, then all data is assumed to be in code page N. Warning: Graphic data will be corrupted when loaded into the database if N is a single-byte code page. |